import pandas as pd
import pymysql
import json
import datetime


def get_db(sql, args=()):
    connect = pymysql.connect(
        host="10.10.4.28",
        database="chinavis_data",
        user="chinavis",
        password="123456",
        port=3306,
        charset='utf8'
    )
    # 获取游标
    cursor = connect.cursor()
    cursor.execute(sql, args)
    connect.commit()
    rv = cursor.fetchall()
    columns = [col[0] for col in cursor.description]
    # 关闭连接
    cursor.close()
    connect.close()
    # return rv
    return [
        dict(zip(columns, row))
        for row in rv
    ]

def city_change(begin_d, end_d, type, main_index):
    category = ['长江中游城市群', '哈长城市群', '成渝城市群', '长江三角洲城市群', '中原城市群',
                '北部湾城市群', '关中平原城市群', '呼包鄂榆城市群', '兰西城市群', '粤港澳大湾区', '京津冀城市群',
                '辽中南城市群', '山东半岛城市群', '海峡西岸城市群']
    sql1 = "select * from cluster_" + type + " where date='" + begin_d + "' and main_index='" + main_index + "'"
    # print(sql1)
    a = get_db(sql1)
    columns = a[0].keys()
    # print(columns)
    data1 = pd.DataFrame(columns=columns, data=a)
    data1 = data1.groupby('category')
    list_pre = []
    for c in data1:
        list1 = list(c[1]['city_name'])
        list_pre.append(list1)
    #print(len(list_pre))
    sql2 = "select * from cluster_" + type + " where date='" + end_d + "' and main_index='"+main_index+"'"
    print(sql1, sql2)
    b = get_db(sql2)
    columns2 = b[0].keys()
    # print(columns)
    data2 = pd.DataFrame(columns=columns2, data=b)
    data2 = data2.groupby('category')
    list_next = []
    for l in data2:
        list2 = list(l[1]['city_name'])
        list_next.append(list2)
    list_list = []
    #print(len(list_next))
    for i in range(len(list_next)):
        list3 = {}
        change = [x for x in list_pre[i] if x not in list_next[i]]
        if change:
            # print(i, change)
            for k in range(len(change)):
                for j in range(len(list_next)):
                    if change[k] in list_next[j]:
                        if j not in list3.keys():
                            listt = []
                            listt.append(change[k])
                            list3[j] = listt
                        else:
                            list3[j].append(change[k])
                        break
            for keys, values in list3.items():
                #print(keys, values)
                change_dict = {}
                change_dict['out_date'] = begin_d
                change_dict['in_date'] = end_d
                change_dict['out_city'] = category[i]
                change_dict['in_city'] = category[keys]
                change_dict['change_city'] = values
                list_list.append(change_dict)
    return list_list

def get_month(type, begin_date, end_date, main_index):
    list1 = begin_date.split('-')
    begin_year = int(list1[0])
    begin_month = int(list1[1])
    #print(begin_year, begin_month)
    list2 = end_date.split("-")
    end_year = int(list2[0])
    end_month = int(list2[1])
    if end_year == 2018 and end_month == 12:
        end_month = end_month - 1
    #print(end_year, end_month)
    all_list = []
    if begin_year == end_year:
        for i in range(begin_month, end_month + 1):
            if i < 10:
                begin_d = str(begin_year) + "-0" + str(i)
                if i + 1 < 10:
                    end_d = str(end_year) + "-0" + str(i+1)
                else:
                    end_d = str(end_year) + "-" + str(i + 1)
            else:
                if i == 12:
                    begin_d = str(begin_year) + "-" + str(i)
                    end_d = str(begin_year + 1) + "-01"
                else:
                    begin_d = str(begin_year) + "-" + str(i)
                    end_d = str(begin_year) + "-" + str(i + 1)
            city_list = city_change(begin_d, end_d, type, main_index)
            for j in range(len(city_list)):
                all_list.append(city_list[j])
    else:
        for year in range(begin_year, end_year+1):
            if year == begin_year:
                for i in range(begin_month, 13):
                    if i < 10:
                        begin_d = str(year) + "-0" + str(i)
                        if i + 1 < 10:
                            end_d = str(year) + "-0" + str(i + 1)
                        else:
                            end_d = str(year) + "-" + str(i + 1)
                    else:
                        if i == 12:
                            begin_d = str(year) + "-" + str(i)
                            end_d = str(year+1) + "-01"
                        else:
                            if i == 12:
                                begin_d = str(year) + "-" + str(i)
                                end_d = str(year + 1) + "-01"
                            else:
                                begin_d = str(year) + "-" + str(i)
                                end_d = str(year) + "-" + str(i + 1)
                    city_list = city_change(begin_d, end_d, type, main_index)
                    for j in range(len(city_list)):
                        all_list.append(city_list[j])
            elif year == end_year:
                for i in range(1, end_month + 1):
                    if i < 10:
                        begin_d = str(year) + "-0" + str(i)
                        if i + 1 < 10:
                            end_d = str(year) + "-0" + str(i + 1)
                        else:
                            end_d = str(year) + "-" + str(i + 1)
                    else:
                        if i == 12:
                            begin_d = str(year) + "-" + str(i)
                            end_d = str(year + 1) + "-01"
                        else:
                            begin_d = str(year) + "-" + str(i)
                            end_d = str(year) + "-" + str(i + 1)
                    city_list = city_change(begin_d, end_d, type, main_index)
                    for j in range(len(city_list)):
                        all_list.append(city_list[j])
            else:
                for i in range(1, 13):
                    if i < 10:
                        begin_d = str(year) + "-0" + str(i)
                        if i + 1 < 10:
                            end_d = str(year) + "-0" + str(i + 1)
                        else:
                            end_d = str(year) + "-" + str(i + 1)
                    else:
                        if i == 12:
                            begin_d = str(year) + "-" + str(i)
                            end_d = str(year + 1) + "-01"
                        else:
                            begin_d = str(year) + "-" + str(i)
                            end_d = str(year) + "-" + str(i + 1)
                    city_list = city_change(begin_d, end_d, type, main_index)
                    for j in range(len(city_list)):
                        all_list.append(city_list[j])
    return all_list

def get_quarter(type, begin_date, end_date, main_index):
    list1 = begin_date.split("-")
    begin_year = int(list1[0])
    begin_quarter = int(list1[1])
    #print(begin_year, begin_quarter)
    list2 = end_date.split("-")
    end_year = int(list2[0])
    end_quarter = int(list2[1])
    if end_year == 2018 and end_quarter == 4:
        end_quarter = end_quarter - 1
    #print(end_year, end_quarter)
    all_list = []
    if begin_year == end_year:
        for i in range(begin_quarter, end_quarter + 1):
            if i == 4:
                begin_d = str(begin_year) + "-" + str(i)
                end_d = str(begin_year+1) + "-1"
            else:
                begin_d = str(begin_year) + "-" + str(i)
                end_d = str(begin_year) + "-" + str(i+1)
            city_list = city_change(begin_d, end_d, type, main_index)
            for j in range(len(city_list)):
                all_list.append(city_list[j])
    else:
        for year in range(begin_year, end_year+1):
            if year == begin_year:
                for i in range(begin_quarter, 5):
                    if i == 4:
                        begin_d = str(year) + "-" + str(i)
                        end_d = str(year + 1) + "-1"
                    else:
                        begin_d = str(year) + "-" + str(i)
                        end_d = str(year) + "-" + str(i + 1)
                    city_list = city_change(begin_d, end_d, type, main_index)
                    for j in range(len(city_list)):
                        all_list.append(city_list[j])
            elif year == end_year:
                for i in range(1, end_quarter+1):
                    if i == 4:
                        begin_d = str(year) + "-" + str(i)
                        end_d = str(year + 1) + "-1"
                    else:
                        begin_d = str(year) + "-" + str(i)
                        end_d = str(year) + "-" + str(i + 1)
                    city_list = city_change(begin_d, end_d, type, main_index)
                    for j in range(len(city_list)):
                        all_list.append(city_list[j])
            else:
                for i in range(1,5):
                    if i == 4:
                        begin_d = str(year) + "-" + str(i)
                        end_d = str(year + 1) + "-1"
                    else:
                        begin_d = str(year) + "-" + str(i)
                        end_d = str(year) + "-" + str(i + 1)
                    city_list = city_change(begin_d, end_d, type, main_index)
                    for j in range(len(city_list)):
                        all_list.append(city_list[j])
    return all_list



def get_week(type, begin_date, end_date, main_index):
    all_list = []
    begin = datetime.datetime.strptime(begin_date, '%Y-%m-%d').date()
    end = datetime.datetime.strptime(end_date, '%Y-%m-%d').date()
    week = datetime.timedelta(days=7)
    if end_date == '2018-12-25':
        end = end - week
    print(end)
    while 1:
        start = begin
        begin = begin + week
        city_list = city_change(start.strftime('%Y-%m-%d'), begin.strftime('%Y-%m-%d'), type, main_index)
        for j in range(len(city_list)):
            all_list.append(city_list[j])
        if begin == end + week:
            break
    return all_list


type = "quarter"
begin_date = "2013-1"
end_date = "2015-4"
main_index = "SO2(微克每立方米)"
if type == "month":
    data = get_month(type, begin_date, end_date, main_index)
elif type == "quarter":
    data = get_quarter(type, begin_date, end_date, main_index)
else:
    data = get_week(type, begin_date, end_date, main_index)
print(data)
# with open('all_list.json', 'w', encoding='utf-8') as file:
#     json.dump(all_list, file, ensure_ascii=False)